# -*- coding: utf-8 -*-
"""
Created on Wed Nov 18 21:47:40 2020
本程序为数据库API

@author: DELL
"""

import pymysql
from pandas import DataFrame

class MysqlDB:
    """操作Mysql的类"""
    def __init__(self, host:str, user:str, password:str, db:str, port:int, charaset:str = 'UTF8'):
        """初始化配置信息"""
        self.host = host 
        self.user = user
        self.password = password
        self.db = db
        self.port = port
        self.charset = charaset
    
    def connect(self):
        """利用配置信息连接MySQL"""
        try:
            self.conn = pymysql.connect(host = self.host,
                                user = self.user,
                                password = self.password,
                                db = self.db,
                                port = self.port,
                                charset = self.charset)
            self.cursor = self.conn.cursor()            
        except Exception as ex:
            print ("Mysql connecting error, reason is " + str(ex))
            raise
            
    def closedb(self):
        """关闭数据库连接"""
        self.cursor.close()
        self.conn.close()
        
    def querydb(self, sql:str, data_format:str = "tuple"):
        """
        执行查询语句
        默认返回元组，若data_format = "dataframe"，则返回datafrme格式
        """
        try:
            self.connect()
            self.cursor.execute(sql)
            result = self.cursor.fetchall()
            if data_format == "dataframe":
                cols = self.cursor.description
                col = []
                for item in cols:
                    col.append(item[0])
                result = DataFrame(result, columns = col)
        except Exception as ex:
            print(ex)
            raise
        self.closedb()
        return result
            
    def insertdb(self, sql:str, num:int = 1, data = None):
        """执行插入语句"""
        try:
            self.connect()
            if num == 1:
                if data == None:
                    self.cursor.execute(sql)
                else:
                    self.cursor.execute(sql, data)
            else:
                self.cursor.executemany(sql, data)
            self.conn.commit()
        except Exception as ex:
            print(ex)
            raise
        self.closedb()
    
    def updatedb(self, sql:str):
        """执行更新语句"""
        return self._edit(sql)
    
    def deletedb(self, sql:str):
        """执行删除语句"""
        return self._edit(sql)

    def _edit(self, sql:str):
        """执行更新和删除语句统一函数"""
        try:
            self.connect()
            self.cursor.execute(sql)
            self.conn.commit()
        except Exception as ex:
            print(ex)
            raise
        self.closedb()
            